package com.base;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Hashtable;
import java.io.*;
import com.utils.Logger;
import com.utils.Md5;
import com.utils.StringTool;

public class DatabaseDAO {

	protected Logger logger = new Logger(this.getClass().getName());

	/**
	 * @param poolName
	 * @param dbTable
	 * @param fields
	 *            {fieldName1, fieldName2, ...}
	 * @param values
	 *            {value1, value2, ...}
	 * @return
	 */
	public boolean insertRow(String poolName, String dbTable, String[] fields,
			Object[] values) {
		if (StringTool.isEmptyOrNul(poolName)
				|| StringTool.isEmptyOrNul(dbTable) || fields == null
				|| fields.length == 0 || values == null || values.length == 0) {
			return false;
		}
		boolean result = false;
		Connection conn = null;
		PreparedStatement preStmt = null;
		String strSql = null;
		try {
			conn = DBPool.getConnection(poolName);
			strSql = "insert into " + dbTable + "(";
			for (int i = 0; i < fields.length; i++) {
				strSql += fields[i] + ",";
			}
			strSql = strSql.substring(0, strSql.length() - 1);
			strSql += ") values (";
			for (int i = 0; i < fields.length; i++) {
				strSql += "?,";
			}
			strSql = strSql.substring(0, strSql.length() - 1);
			strSql += ")";

			// prepare statement
			preStmt = conn.prepareStatement(strSql);

			// bind parameters
			for (int i = 0; i < values.length; i++) {
				preStmt.setObject(i + 1, values[i]);
			}

			if (preStmt.executeUpdate() == 1) {
				result = true;
			}
			if (result) {
				conn.commit();
			}

		} catch (Exception e) {
			logger.error("insert: Error executing " + strSql + " >>> "
					+ e.toString());
		} finally {
			DBPool.releaseConnection(poolName, conn, preStmt);
		}
		return result;
		
	}

	public boolean executeSQLStatement(String poolName, String sql) {
		if (StringTool.isEmptyOrNul(poolName)) {
			return false;
		}
		boolean result = false;
		Connection conn = null;
		PreparedStatement preStmt = null;
		String strSql = sql;
		try {
			conn = DBPool.getConnection(poolName);
			// prepare statement
			preStmt = conn.prepareStatement(strSql);

			if (preStmt.executeUpdate() == 1) {
				result = true;
			}
			conn.commit();
		} catch (Exception e) {
			logger.error("execute: Error executing " + strSql + " >>> "
					+ e.toString());
		} finally {
			DBPool.releaseConnection(poolName, conn, preStmt);
		}
		return result;
	}
	

	/**
	 * @param poolName
	 * @param dbTable
	 * @param where
	 *            "fieldName1=? and fieldName2 = ?"
	 * @param whereValues
	 *            {value1, value2}
	 * @return true or false
	 */
	public boolean deleteRow(String poolName, String dbTable, String where,
			Object[] whereValues) {
		if (StringTool.isEmptyOrNul(poolName)
				|| StringTool.isEmptyOrNul(dbTable)) {
			return false;
		}
		Connection conn = null;
		PreparedStatement preStmt = null;
		String strSql = null;
		boolean result = false;
		try {
			conn = DBPool.getConnection(poolName);
			strSql = "delete from " + dbTable;
			if (!StringTool.isEmptyOrNul(where)) {
				strSql += " where " + where;
			}
			preStmt = conn.prepareStatement(strSql);
			if (!StringTool.isEmptyOrNul(where)) {
				// bind parameters
				for (int i = 0; i < whereValues.length; i++) {
					preStmt.setObject(i + 1, whereValues[i]);
				}
			}
			if (preStmt.executeUpdate() > 0) {
				result = true;
			}
		} catch (Exception _e) {
			logger.error("deleteRow: Error executing " + strSql + " >>> "
					+ _e.toString());
		} finally {
			DBPool.releaseConnection(poolName, conn, preStmt);
		}
		return result;

	}

	/**
	 * @param poolName
	 * @param dbTable
	 * @param fields
	 *            {fieldName1, fieldName2, ...}
	 * @param values
	 *            {value1, value2, ...}
	 * @param where
	 *            "fieldName1=? and fieldName2 = ?"
	 * @param whereValues
	 *            {value1, value2}
	 * @return
	 */
	public boolean updateRow(String poolName, String dbTable, String[] fields,
			Object[] values, String where, Object[] whereValues) {
		if (StringTool.isEmptyOrNul(poolName)
				|| StringTool.isEmptyOrNul(dbTable) || fields == null
				|| fields.length == 0 || values == null || values.length == 0) {
			return false;
		}
		boolean result = false;
		Connection conn = null;
		PreparedStatement preStmt = null;
		String strSql = null;
		try {
			conn = DBPool.getConnection(poolName);
			strSql = "update " + dbTable + " set ";
			for (int i = 0; i < fields.length; i++) {
				strSql += fields[i] + "=?,";
			}
			strSql = strSql.substring(0, strSql.length() - 1);
			strSql += " where " + where;

			// prepare statement
			preStmt = conn.prepareStatement(strSql);

			// bind parameters
			int index = 0;
			for (int i = 0; i < values.length; i++) {
				preStmt.setObject(++index, values[i]);
			}
			for (int i = 0; i < whereValues.length; i++) {
				preStmt.setObject(++index, whereValues[i]);
			}

			if (preStmt.executeUpdate() == 1) {
				result = true;
			}
			if (result) {
				conn.commit();
			}

		} catch (Exception e) {
			logger.error("updateRow: Error executing " + strSql + " >>> "
					+ e.toString());
		} finally {
			DBPool.releaseConnection(poolName, conn, preStmt);
		}
		return result;
	}
	public ArrayList<DatabaseObject> findByDynamicWhereContentGroup(String poolName,
			String dbTable, String[] fields, String[] alias, String where,
			String orderBy) {
		// declare variables
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		ArrayList<DatabaseObject> result = null;

		try {
			conn = DBPool.getConnection(poolName);
			strSql = "select ";
			for (int i = 0; i < fields.length; i++) {
				strSql += fields[i]
						+ (StringTool.isEmptyOrNul(alias[i]) ? "" : " as "
								+ alias[i]) + ",";
			}
			strSql = strSql.substring(0, strSql.length() - 1);
			strSql += " from " + dbTable;

			if (!StringTool.isEmptyOrNul(where)) {
				strSql += " where " + where;
			}
			if (!StringTool.isEmptyOrNul(orderBy)) {
				strSql += " order by " + orderBy;
			}
			// prepare statement
			preStmt = conn.prepareStatement(strSql);

			// bind parameters
			//if (!StringTool.isEmptyOrNul(where) && whereValues != null) {
			//	for (int i = 0; i < whereValues.length; i++) {
			//		preStmt.setObject(i + 1, whereValues[i]);
					// System.out.println(whereValues[i]);
			//	}
			//}
			logger.log("sql stamenet " + strSql);
			System.out.println(strSql);
			rs = preStmt.executeQuery();
			result = new ArrayList<DatabaseObject>();
			while (rs.next()) {
				DatabaseObject object = new DatabaseObject();
				Hashtable<String, Object> attribute = new Hashtable<String, Object>();
				for (int i = 0; i < fields.length; i++) {
					Object o = rs
							.getObject(StringTool.isEmptyOrNul(alias[i]) ? fields[i]
									: alias[i]);
					if (o != null) {
						attribute.put(
								StringTool.isEmptyOrNul(alias[i]) ? fields[i]
										: alias[i], o);
					}
				}
				object.setAttribute(attribute);
				result.add(object);
			}

		} catch (Exception _e) {
			logger.error("findByDynamicWhere: Error executing " + strSql
					+ " >>> " + _e.toString());
			_e.printStackTrace();
		} finally {
			DBPool.releaseConnection(poolName, conn, preStmt, rs);
		}
		return result;
	}

	public ArrayList<DatabaseObject> findByDynamicWhere(String poolName,
			String dbTable, String[] fields, String[] alias, String where,
			Object[] whereValues, String orderBy) {
		// declare variables
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		ArrayList<DatabaseObject> result = null;

		try {
			conn = DBPool.getConnection(poolName);
			strSql = "select ";
			for (int i = 0; i < fields.length; i++) {
				strSql += fields[i]
						+ (StringTool.isEmptyOrNul(alias[i]) ? "" : " as "
								+ alias[i]) + ",";
			}
			strSql = strSql.substring(0, strSql.length() - 1);
			strSql += " from " + dbTable;

			if (!StringTool.isEmptyOrNul(where)) {
				strSql += " where " + where;
			}
			if (!StringTool.isEmptyOrNul(orderBy)) {
				strSql += " order by " + orderBy;
			}
			// prepare statement
			preStmt = conn.prepareStatement(strSql);

			// bind parameters
			if (!StringTool.isEmptyOrNul(where) && whereValues != null) {
				for (int i = 0; i < whereValues.length; i++) {
					preStmt.setObject(i + 1, whereValues[i]);
					// System.out.println(whereValues[i]);
				}
			}
			logger.log("sql stamenet " + strSql);
			System.out.println(strSql);
			rs = preStmt.executeQuery();
			result = new ArrayList<DatabaseObject>();
			while (rs.next()) {
				DatabaseObject object = new DatabaseObject();
				Hashtable<String, Object> attribute = new Hashtable<String, Object>();
				for (int i = 0; i < fields.length; i++) {
					Object o = rs
							.getObject(StringTool.isEmptyOrNul(alias[i]) ? fields[i]
									: alias[i]);
					if (o != null) {
						attribute.put(
								StringTool.isEmptyOrNul(alias[i]) ? fields[i]
										: alias[i], o);
					}
				}
				object.setAttribute(attribute);
				result.add(object);
			}

		} catch (Exception _e) {
			logger.error("findByDynamicWhere: Error executing " + strSql
					+ " >>> " + _e.toString());
			_e.printStackTrace();
		} finally {
			DBPool.releaseConnection(poolName, conn, preStmt, rs);
		}
		return result;
	}

	public ArrayList<DatabaseObject> findByPage(String poolName,
			String dbTable, String[] fields, String[] alias, String where,
			Object[] whereValues, String orderBy, long currPage,
			long rowsPerPage) {
		// declare variables
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		ArrayList<DatabaseObject> result = null;

		try {
			conn = DBPool.getConnection(poolName);
			strSql = "select ";
			for (int i = 0; i < fields.length; i++) {
				strSql += fields[i]
						+ (StringTool.isEmptyOrNul(alias[i]) ? "" : " as "
								+ alias[i]) + ",";
			}
			strSql = strSql.substring(0, strSql.length() - 1);
			strSql += " from " + dbTable;

			if (!StringTool.isEmptyOrNul(where)) {
				strSql += " where " + where;
			}
			if (!StringTool.isEmptyOrNul(orderBy)) {
				strSql += " order by " + orderBy;
			}
			long startRec = (currPage - 1) * rowsPerPage;
			if (startRec >= 0) {
				strSql += " limit " + startRec + ", " + rowsPerPage;
			}
			// prepare statement
			preStmt = conn.prepareStatement(strSql);

			// bind parameters
			if (!StringTool.isEmptyOrNul(where) && whereValues != null) {
				for (int i = 0; i < whereValues.length; i++) {
					preStmt.setObject(i + 1, whereValues[i]);
				}
			}

			rs = preStmt.executeQuery();
			result = new ArrayList<DatabaseObject>();
			while (rs.next()) {
				DatabaseObject object = new DatabaseObject();
				Hashtable<String, Object> attribute = new Hashtable<String, Object>();
				for (int i = 0; i < fields.length; i++) {
					Object o = rs
							.getObject(StringTool.isEmptyOrNul(alias[i]) ? fields[i]
									: alias[i]);
					if (o != null) {
						attribute.put(
								StringTool.isEmptyOrNul(alias[i]) ? fields[i]
										: alias[i], o);
					}
				}
				object.setAttribute(attribute);
				result.add(object);
			}

		} catch (Exception _e) {
			logger.error("findByDynamicWhere: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(poolName, conn, preStmt, rs);
		}
		return result;
	}

	public long countByDynamicWhere(String poolName, String dbTable,
			String where, Object[] whereValues) {
		// declare variables
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		long result = 0;

		try {
			conn = DBPool.getConnection(poolName);
			strSql = "select count(*) from " + dbTable;

			if (!StringTool.isEmptyOrNul(where)) {
				strSql += " where " + where;
			}
			// prepare statement
			preStmt = conn.prepareStatement(strSql);

			// bind parameters
			if (!StringTool.isEmptyOrNul(where) && whereValues != null) {
				for (int i = 0; i < whereValues.length; i++) {
					preStmt.setObject(i + 1, whereValues[i]);
				}
			}

			rs = preStmt.executeQuery();
			if (rs.next()) {
				result = rs.getLong(1);
			}

		} catch (Exception _e) {
			logger.error("countByDynamicWhere: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(poolName, conn, preStmt, rs);
		}
		return result;
	}

	public Hashtable<String, String> sumGroupBy(String poolName, String strSql,
			Object[] fillValues, String[] sKeyField, String sDieuKien) {
		// declare variables
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		Hashtable<String, String> result = new Hashtable<String, String>();

		try {
			conn = DBPool.getConnection(poolName);

			// prepare statement
			preStmt = conn.prepareStatement(strSql);

			if (fillValues != null && fillValues.length > 0) {
				for (int i = 0; i < fillValues.length; i++) {
					preStmt.setObject((i + 1), fillValues[i]);
				}
			}

			rs = preStmt.executeQuery();
			while (rs.next()) {
				if (result.get(rs.getString(sDieuKien)) == null) {
					String sValue = "";
					for (int i = 0; i < sKeyField.length; i++) {
						sValue += "-" + rs.getObject(sKeyField[i]);
					}
					result.put(rs.getString(sDieuKien), sValue);
				}
			}

		} catch (Exception _e) {
			logger.error("sumGroupBy: Error executing " + strSql + " >>> "
					+ _e.toString());
		} finally {
			DBPool.releaseConnection(poolName, conn, preStmt, rs);
		}
		return result;
	}

	public static void main(String[] args) {
		DatabaseDAO databaseDAO = new DatabaseDAO();
		String[] fields = { "username", "password", "fullname", "phone",
				"email" };
		String[] values = { "phuongnt", Md5.Hash("110923"),
				"Nguyen Thien Phuong", "0985104405", "nthienphuong@gmail.com" };
		System.out.println(databaseDAO.insertRow("mwap", "admin_account",
				fields, values));
	}
}
